[Oracle]SQL*Loader制御ファイルを生成する手軽な方法

甲木洋介

2016.05.23

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

こんにちは、yokatsukiです。

相変わらずOracle DBを触る機会がちょくちょくあるのですが、先日こういう相談を受けました。

「SQL*Loaderで複数テーブルのロードを行うことになったのだが、手書きでひとつひとつ制御ファイルを作成するのがめんどい。何か自動生成する方法はないか？」

ということでお手軽な方法がないか調べてみました。

SQL*Loader制御ファイルの生成

ズバリ「Oracle SQL Developer(以下SQL Developer)を使おう」です。
ただし、条件としてロード対象のテーブルがデータベース内に作成できていることが条件です。

以下の環境で確認しました。

Windows 10 Pro
SQL Developer 4.1.3.20 w/jdk1.8.0_92
RDS for Oracle 12.1.0.1v4

Oracleデータベースの確認

SQL*Loader制御ファイルを作成したい対象のテーブルにSQL Developerからアクセスできるようにしておきます。

今回はサンプルとして、日本郵政からダウンロードできる郵便番号データを準備しました。CREATE TABLE文は以下です。

CREATE TABLE zipcode( 
    organization_code NUMBER(5), 
    old_zip CHAR(5), 
    new_zip CHAR(7), 
    pref_kana VARCHAR2(7), 
    city_kana VARCHAR2(25), 
    street_kana VARCHAR2(80), 
    pref_kanji VARCHAR2(10), 
    city_kanji VARCHAR2(25), 
    street_kanji VARCHAR2(80), 
    flag_1 CHAR(1), 
    flag_2 CHAR(1), 
    flag_3 CHAR(1), 
    flag_4 CHAR(1), 
    flag_5 CHAR(1), 
    flag_6 CHAR(1)
);

Oracle SQL Developerのインストールからデータベース接続まで

Oracle SQL Developer(以下SQL Developer)のインストールや起動についてはインストレーション・ガイドがあるのでそちらをご覧ください。大まかには以下の手順です。難しくはありません。

JDKインストール
SQL Developerのzipファイルダウンロード&解凍
中の.exeファイル実行、(初回起動時のみ)JDKの場所指定

インストールが完了したら、SQL Developerを起動して、

Oracle DBへ接続する設定を書きます。

SQL*Loader制御ファイルの出力

さて、ここからが本題なのですが、SQL*Loader制御ファイルの出力は、テーブルのエクスポート設定で出力フォーマットの一つとして用意されています。

まず、SQL Developerの接続ツリーから目的のテーブルを右クリックして、"エクスポート"を選択します。

エクスポート・ウィザードで"DDLのエクスポートチェック"を外します(①)。
(DDL文の作成が必要な方は、ここを選択しておいても構いません)
そして、データのエクスポートのチェックを有効にしたままで、フォーマットのドロップダウンリストから"loader"を選択します(②)。

次に"データのエクスポート"の囲みの中の項目を、実際にロードしたいcsvデータと同じ形式に設定します(③)。

次に進む前に、"別名保存"のドロップダウンリストから"個別ファイル"を選択しておいてください(④)。
SQL*Loader形式での出力の場合、データファイルとSQL*Loader制御ファイル等、複数のファイルが作成される為、"単一ファイル"設定のままだとエラーが発生します。

次へ進みます。
ここでは出力データに条件句を付け加えることができるのですが、本題の目的はSQL*Loader制御ファイルを作成することなので、ここはスルーします。

(試しにここのWHERE句に、データを出さない目的で"1=2"と記入しましたが、その際にはエラーもファイルも出力されずに終了しました)

最後の画面はサマリーです。念の為フォルダを開いて出力先フォルダ名など設定を確認します。

ロードが開始されます。この時バックグランドでは"<テーブル名>_DATA_TABLE.ldr"という名のファイルが作成され、データが出力されていますが、不要であればタスクの取消を実行して構いません。タスクが完了もしくは取り消しされると、SQL*Loader制御ファイルが生成されます。

SQL*Loader制御ファイルの確認

生成されたSQL*Loader制御ファイルをエディタで開いてみます。

OPTIONS (ERRORS=12827)
LOAD DATA 
INFILE 'C:\Users\katsuki.yosuke\ZIPCODE_DATA_TABLE.ldr' "str '\r\n'"
APPEND
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE "MASTER"."ZIPCODE"
FIELDS TERMINATED BY','
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS ( 
"ORGANIZATION_CODE" ,
"OLD_ZIP" CHAR (5),
"NEW_ZIP" CHAR (7),
"PREF_KANA" CHAR (7),
"CITY_KANA" CHAR (25),
"STREET_KANA" CHAR (80),
"PREF_KANJI" CHAR (10),
"CITY_KANJI" CHAR (25),
"STREET_KANJI" CHAR (80),
"FLAG_1" CHAR (1),
"FLAG_2" CHAR (1),
"FLAG_3" CHAR (1),
"FLAG_4" CHAR (1),
"FLAG_5" CHAR (1),
"FLAG_6" CHAR (1))

一通り必要な記述が網羅されています。INFILE句をロード対象ファイルに書き換えてsqlldrコマンドを実行すればデータロード完了です。

SQL*Loader制御ファイルの詳細については、Oracle Databaseユーティリティマニュアルの中にリファレンスがあるのでそちらを参照してください。

まとめ

SQL Developerを使用することで、SQL*Loader制御ファイルが簡単に生成できることを確認しました。非常に簡単でしたね。

SQL*Loader制御ファイルはエディタで手書きという認識が多いようなので、秘伝のタレのように既存のSQL*Loader制御ファイルを書き換えて使用されている方も少なくないと思います。この機会に一旦リファレンスを見直してはいかがでしょうか。それぞれの句が示す内容をしっかり理解し、無用な設定不明な設定が無いようにしましょう。

それでは、また。